package wstrader.task.home.futures;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;

import wstrader.task.trade.*;

import pc.sql.value.*;

import pc.system.*;
import pc.util.*;

import wstrader.task.GenReport;

public class DailyBasis extends TradeTask
{
  public DailyBasis(Tasks owner)
  {
    super(owner);
  }
  
  public java.sql.Date doTask()
  {
    int task_id     = this.getRequest().getIntValue("task_id");
    java.sql.Date focused_id = this.getRequest().getValueDate("futures_daily_basis");//Utils.strToDate(this.getRequest().getStringValue("db_date"));
    int RecId = this.getRequest().getIntValue("rec_id");
    switch (task_id)
	{
      case 1 : // Update
//      	if (RecId > 0)
//      	{
      		focused_id = updateRecord();
//      	}
        break;
      case 2 : // Delete

        break;
      default : //View only
        break;
    }

    return focused_id;
  }
 
//inserted by Nara - begin
  public ResultSet fetchFuturesDailyBasisList()
  {
  	java.sql.Date DbDt = getRequest().getValueDate("fdb_date");
  	
  	if (DbDt.compareTo(new java.sql.Date(0))== 0) DbDt = this.maxDt();
  	
  	String Sql ="Select * from futures_daily_basis where " +
  				"(daily_basis_date = ? OR ?)  order by daily_basis_date desc,id";
    Vector vp = new Vector();	

    vp.addElement(new DateValue(DbDt));
    vp.addElement(new BooleanValue(DbDt.compareTo(new java.sql.Date(0)) == 0));

  	return getSQLCommand().executeQuery(Sql,vp);
  }
  
  public java.sql.Date updateRecord()
  {
  	String  TrnDt=getRequest().getStringValue("daily_basis_date");
  	double  Ex_Rt=getRequest().getDoubleValue("rate");
  	int  Ex_Rt_Id=getRequest().getIntValue("ex_rt_id");
  	
  	java.sql.Date Tdt= new java.sql.Date(0);
  	

  	if (TrnDt.compareTo("")!=0)
  	{
  		Tdt= Utils.strToDate(TrnDt);
  	}
  	else
  	{
  		Tdt= new java.sql.Date(0);
  	}
  	int RecNo = 0;
  	int RecNo_Ex = 0;
  	String Sql ="";
  	if (TrnDt.compareTo("")!=0)
  	{
  		ResultSet rc = getSQLCommand().executeQuery("select count(*) as no from futures_daily_basis where daily_basis_date = '" + Tdt + "'");
  		ResultSet re = getSQLCommand().executeQuery("select count(*) as no_ex from exchange_rate_master where date = '" + Tdt + "'");
  	try 
	{
		rc.beforeFirst();
		if (rc.next()== true)
		{
			RecNo = rc.getInt("no");
		}
		else
		{
			RecNo=0;
		}
		rc.close();
		
		re.beforeFirst();
		if (re.next()== true)
		{
			RecNo_Ex = re.getInt("no_ex");
		}
		else
		{
			RecNo_Ex=0;
		}
		re.close();
		
		int loop =1;
		while (loop < 6)
		{
			long IdArb = Utils.strToLong(getRequest().getStringValue("id_arb"+loop));
			String MOne = getRequest().getStringValue("ter_mth_arb"+loop).substring(0,1);
			String MRest = getRequest().getStringValue("ter_mth_arb"+loop).substring(1,3);
			String Yrs =getRequest().getStringValue("ter_mth_arb"+loop).substring(4);
			
			//String StrTArb ="01-" + getRequest().getStringValue("ter_mth_arb"+loop);
			String StrTArb ="01-" + MOne.toUpperCase()+MRest.toLowerCase() + "-" + Yrs;
			java.sql.Date TerMthArb = Utils.strToDate(StrTArb.trim());
			double CloseArb = getRequest().getDoubleValue("close_arb"+loop);
			double LowArb = getRequest().getDoubleValue("low_arb"+loop);
			double HighArb = getRequest().getDoubleValue("high_arb"+loop);
			double BpArb = getRequest().getDoubleValue("bp_arb"+loop);
			
			if (RecNo==0)
			{
			 	Sql ="insert into futures_daily_basis set " +
				"daily_basis_date = ?, " +
  				"terminal_market = ?, " +
				"terminal_month = ?, " +
				"closing_price = ?, " +
				"low_price = ?, " +
				"high_price = ?, " +
				"basis_price = ?";
			 	
			 	Vector vp = new Vector();

			 	vp.addElement(new DateValue(Tdt));
			 	vp.addElement(new StringValue("NYC"));
			 	vp.addElement(new DateValue(TerMthArb));
			 	vp.addElement(new DoubleValue(CloseArb));
			 	vp.addElement(new DoubleValue(LowArb));
			 	vp.addElement(new DoubleValue(HighArb));
			 	vp.addElement(new DoubleValue(BpArb));
  	
			 	int i= getSQLCommand().executeUpdate(Sql,vp);			 	
			}
			else
			{
			 	Sql ="update futures_daily_basis set " +
				"daily_basis_date = ?, " +
  				"terminal_market = ?, " +
				"terminal_month = ?, " +
				"closing_price = ?, " +
				"low_price = ?, " +
				"high_price = ?, " +
				"basis_price = ? where id = ?";

			 	Vector vp = new Vector();

			 	vp.addElement(new DateValue(Tdt));
			 	vp.addElement(new StringValue("NYC"));
			 	vp.addElement(new DateValue(TerMthArb));
			 	vp.addElement(new DoubleValue(CloseArb));
			 	vp.addElement(new DoubleValue(LowArb));
			 	vp.addElement(new DoubleValue(HighArb));
			 	vp.addElement(new DoubleValue(BpArb));
			 	vp.addElement(new LongValue(IdArb));

			 	int i= getSQLCommand().executeUpdate(Sql,vp);
			}
			loop=loop+1;
		}
		
// ROBUSTA		
		
		loop =1;
		while (loop < 7)
		{
			int IdArb = getRequest().getIntValue("id_rbs"+loop);
			String MOne = getRequest().getStringValue("ter_mth_rbs"+loop).substring(0,1);
			String MRest = getRequest().getStringValue("ter_mth_rbs"+loop).substring(1,3);
			String Yrs =getRequest().getStringValue("ter_mth_rbs"+loop).substring(4);
			String StrTRbs ="01-" + MOne.toUpperCase()+MRest.toLowerCase() + "-" + Yrs;
			
//			String StrTRbs= "01-" + getRequest().getStringValue("ter_mth_rbs"+loop);
			java.sql.Date TerMthArb = Utils.strToDate(StrTRbs.trim());
			double CloseArb = getRequest().getDoubleValue("close_rbs"+loop);
			double LowArb = getRequest().getDoubleValue("low_rbs"+loop);
			double HighArb = getRequest().getDoubleValue("high_rbs"+loop);
			double BpArb = getRequest().getDoubleValue("bp_rbs"+loop);
			
			if (RecNo==0)
			{
			 	Sql ="insert into futures_daily_basis set " +
				"daily_basis_date = ?, " +
  				"terminal_market = ?, " +
				"terminal_month = ?, " +
				"closing_price = ?, " +
				"low_price = ?, " +
				"high_price = ?, " +
				"basis_price = ?";
			 	
			 	Vector vp = new Vector();

			 	vp.addElement(new DateValue(Tdt));
			 	vp.addElement(new StringValue("LIFFE"));
			 	vp.addElement(new DateValue(TerMthArb));
			 	vp.addElement(new DoubleValue(CloseArb));
			 	vp.addElement(new DoubleValue(LowArb));
			 	vp.addElement(new DoubleValue(HighArb));
			 	vp.addElement(new DoubleValue(BpArb));
  	
			 	int i= getSQLCommand().executeUpdate(Sql,vp);			 	
			}
			else
			{
			 	Sql ="update futures_daily_basis set " +
				"daily_basis_date = ?, " +
  				"terminal_market = ?, " +
				"terminal_month = ?, " +
				"closing_price = ?, " +
				"low_price = ?, " +
				"high_price = ?, " +
				"basis_price = ? where id = ?";
			 	
			 	Vector vp = new Vector();

			 	vp.addElement(new DateValue(Tdt));
			 	vp.addElement(new StringValue("LIFFE"));
			 	vp.addElement(new DateValue(TerMthArb));
			 	vp.addElement(new DoubleValue(CloseArb));
			 	vp.addElement(new DoubleValue(LowArb));
			 	vp.addElement(new DoubleValue(HighArb));
			 	vp.addElement(new DoubleValue(BpArb));
			 	vp.addElement(new IntValue(IdArb));
			 	
			 	int i= getSQLCommand().executeUpdate(Sql,vp);			 	
			}
			loop=loop+1;
		}	

		if (RecNo_Ex==0)
		{
		 	Sql ="insert into exchange_rate_master set " +
			"date = ?, " +
			"usd_to_vnd = ?";
		 	
		 	Vector vp = new Vector();

		 	vp.addElement(new DateValue(Tdt));
		 	vp.addElement(new DoubleValue(Ex_Rt));
	
		 	int i= getSQLCommand().executeUpdate(Sql,vp);			 	
		}
		else
		{
		 	Sql ="update exchange_rate_master set " +
			"date = ?, " +
			"usd_to_vnd = ? where id = ?";
		 	
		 	Vector vp = new Vector();

		 	vp.addElement(new DateValue(Tdt));
		 	vp.addElement(new DoubleValue(Ex_Rt));
		 	vp.addElement(new IntValue(Ex_Rt_Id));
		 	
		 	int i= getSQLCommand().executeUpdate(Sql,vp);	
		}		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
  	}
 	return Tdt;
  }
  //inserted by nara - ends  
  

  public ResultSet fetchFuturesDailyBasisRecord()
  {
  	java.sql.Date DbDt = getRequest().getValueDate("futures_daily_basis");
  	return getSQLCommand().executeQuery("select * from futures_daily_basis where daily_basis_date = '" +DbDt +"' order by terminal_market,terminal_month");
  }  
  
  public ResultSet fetchExchangeRateRecord()
  {
  	java.sql.Date DbDt = getRequest().getValueDate("futures_daily_basis");
  	return getSQLCommand().executeQuery("select * from exchange_rate_master where date = '" +DbDt +"'");
  }
  
  private java.sql.Date maxDt()
  {

  	java.sql.Date Ret =new java.sql.Date(0);
  	ResultSet Rs = getSQLCommand().executeQuery("select daily_basis_date from futures_daily_basis order by daily_basis_date desc");
  	try {
		Rs.beforeFirst();
		if (Rs.next()==true)
		{
			Rs.next();
			Ret = Rs.getDate("daily_basis_date");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
  		return Ret;
  }
  
  public void callExcel()
  {
  	CreateTerMth TM = new CreateTerMth(this);
  	TM.readExcel();
  }
}

class CreateTerMth extends GenReport
{
	private DailyBasis DB = null;
	
	  public CreateTerMth(Tasks owner)
	  {
	  	super(owner,"Master Stats-Nara.xls");
	    this.DB = (DailyBasis)owner;
	  }	
	  
	  public void readExcel()
	  {
	  	int dRow = 2;
	  	
		HSSFRow row ;
		HSSFCell cell;

	  	while (dRow <= 383)
	  	{
	  		row = this.getRow(dRow);
	  		cell = row.getCell((short)0);
	  		
	  		java.util.Date dbDt = cell.getDateCellValue();
	  		String s_dbDt = Utils.dateToStr(dbDt);
	  		java.sql.Date d_dbDt = Utils.strToDate(s_dbDt);
	  		
	  		cell = row.getCell((short)1);
	  		double val = cell.getNumericCellValue();
	  		
	  		DB.createTerminalPriceRecord(d_dbDt,"LIFFE",val);
	  		dRow=dRow+1;
	  	}
	  }
}